﻿CREATE proc [dbo].[GetOrganisation]
	@OrgKey			int				= null,
	@Suchbegriff	nvarchar(50)	= null,
	@Name			nvarchar(50)	= null,
	@Plz			int				= null
as
begin
	set nocount on

	select 
		coalesce(o.OrgKey, 0)			'OrgKey',
		coalesce(o.OrgTS,'')			'OrgTS',
		coalesce(o.Name,'')				'OrgName',
		coalesce(o.Prefix,'')			'Prefix',
		coalesce(o.ZusätzeFW,'')		'ZusätzeFW',
		coalesce(o.Suchbegriff,'')		'Suchbegriff',
		coalesce(o.Adresse, '')			'Adresse',
		coalesce(o.Straße,'')			'Straße',
		coalesce(o.TelNr1,'')			'TelNr1',
		coalesce(o.TelNr2,'')			'TelNr2',
		coalesce(o.FaxNr, '')			'FaxNr',
		coalesce(o.EPLZ, 0)				'EPLZ',
		coalesce(o.Abteilung,'')		'Abteilung',
		coalesce(p.Plz,'')				'Plz',
		coalesce(p.Ort,'')				'Ort',
		coalesce(o.e_Mail,'')			'EMail',
		coalesce(o.Kurzwahlspeicher,'')	'Kurzwahlspeicher',
		coalesce(o.Notiz, '')			'Notiz',
		coalesce(o.Export, 0)			'Export',
		coalesce(o.[UID],'')			'UID',
		coalesce(o.[Name],'') 
			+ coalesce(' (' + o.Prefix + '), ','') 
			+ coalesce(o.ZusätzeFW + ', ','') 
			+ coalesce(o.Suchbegriff + ', ','') 
			+ coalesce(o.Adresse + ' - ','')  
			+ coalesce(o.Straße,'')		'OrgData',
		coalesce(o.[CreateOn],'')		'CreatedOn',
		coalesce(o.[CreateBy],'')		'CreatedBy',
		coalesce(o.[LastChangedOn],'')	'LastChangedOn',
		coalesce(o.[LastChangedBy],'')	'LastChangedBy',
		coalesce(o.KG,'')				'KG',
		coalesce(o.MB,'')				'MB',
		coalesce(rf.RechtsFBez,'')		'RechtsFBez',
		coalesce(o.RechtsFKey, 0)		'RechtsFKey',
		coalesce(o.TochterVon,'')		'TochterVon',
		coalesce(o.KreditLimit,'')		'KreditLimit',
		coalesce(o.Export,'')			'Export',
		coalesce(o.Web, '')				'Web',
		coalesce(o.[UID], '')			'UID'
	from dbo.Organisation o
		left join dbo.Postleitzahl p on
			p.EPLZ = o.EPLZ
		left join dbo.Rechtsform rf on
			rf.RechtsFKey = o.RechtsFKey
	where (o.OrgKey = @OrgKey or @OrgKey is null)
			and (o.Suchbegriff = @Suchbegriff or @Suchbegriff is null)
			and (o.[Name] like @Name or @Name is null)
			and (p.PLZ = cast(@Plz as nvarchar(10)) or @Plz is null)
	order by o.[Name]
end
GO
GRANT EXECUTE
    ON OBJECT::[dbo].[GetOrganisation] TO PUBLIC
    AS [dbo];

